﻿Imports Microsoft.LightSwitch.Threading
Imports System.Windows.Controls
Imports DocumentToolkit.Client
Imports System.Text
Imports ComponentPro.Excel
Imports ComponentPro.Excel.Charts
Imports System.Diagnostics


Namespace LightSwitchApplication

    Public Class PersonsListDetail

        Private Sub PersonsListDetail_Created()
            Dim Upload = Me.FindControl("Upload")
            AddHandler Upload.ControlAvailable, AddressOf Upload_ControlAvailable

            Dim ExcelButton = Me.FindControl("ExcelExport")
            AddHandler ExcelButton.ControlAvailable, AddressOf ExcelExport_ControlAvailable

        End Sub

        Private Sub Upload_ControlAvailable(sender As Object, e As ControlAvailableEventArgs)
            RemoveHandler Me.FindControl("Upload").ControlAvailable, AddressOf Upload_ControlAvailable
            Dim Button = DirectCast(e.Control, Button)
            AddHandler Button.Click, AddressOf Upload_Click

        End Sub

        Private Sub Upload_Click(sender As Object, e As System.Windows.RoutedEventArgs)
            Dim result As FileInfo = Nothing
            Dispatchers.Main.Invoke(
            Sub()
                Dim dlg = New OpenFileDialog()
                dlg.Filter = "Mutt Scout Images|*.pdf;*.xps;*.jpg;*.png"
                If dlg.ShowDialog = True Then
                    Dim file = dlg.File
                    If Not file Is Nothing Then
                        Me.Details.Dispatcher.BeginInvoke(
                            Sub()
                                'Dim reader = DocumentReader.Create(file)
                                'Using reader
                                'Dim document = Me.MediaCollection.AddNew
                                'document.FileName = file.Name
                                'document.File = reader.ToBinary

                                'If reader.RequiresConversionToXps Then
                                '' conversion needed, get the XPS binary
                                'document.FileXps = reader.ToXpsBinary
                                'End If
                                'End Using
                                'End Sub)

                                Dim bData As Byte()
                                Dim br As BinaryReader = New BinaryReader(file.OpenRead())
                                bData = br.ReadBytes(br.BaseStream.Length)
                                'Dim ms As MemoryStream = New MemoryStream(bData, 0, bData.Length)


                                Dim document = Me.MediaCollection.AddNew()
                                document.FileName = file.Name
                                document.File = bData
                                br.Close()
                            End Sub)
                    End If
                End If
            End Sub)

        End Sub

        Private Sub PersonsListDetail_InitializeDataWorkspace(saveChangesTo As List(Of IDataService))
            If Application.User.HasPermission(Permissions.CanUpdatePeople) Then
                Me.FindControl("Upload").IsVisible = True
                Me.FindControl("MediaCollection_DeleteSelected").IsVisible = True
                Me.FindControl("Flag_AddAndEditNew").IsVisible = True
                Me.FindControl("Flag_EditSelected").IsVisible = True
                Me.FindControl("Flag_DeleteSelected").IsVisible = True
                Me.FindControl("ExcelExport").IsVisible = True
            Else
                Me.FindControl("Upload").IsVisible = False
                Me.FindControl("MediaCollection_DeleteSelected").IsVisible = False
                Me.FindControl("Flag_AddAndEditNew").IsVisible = False
                Me.FindControl("Flag_EditSelected").IsVisible = False
                Me.FindControl("Flag_DeleteSelected").IsVisible = False
                Me.FindControl("ExcelExport").IsVisible = False
            End If

        End Sub


        Private Sub ExcelExport_ControlAvailable(sender As Object, e As ControlAvailableEventArgs)
            RemoveHandler Me.FindControl("ExcelExport").ControlAvailable, AddressOf ExcelExport_ControlAvailable
            Dim Button = DirectCast(e.Control, Button)
            AddHandler Button.Click, AddressOf ExcelExport_Click

            'On Error Resume Next
            'AddHandler CType(e.Control, System.Windows.Controls.Button).GotFocus, AddressOf ExportCSV_Click

        End Sub

        Private Sub ExcelExport_Click(sender As Object, e As System.Windows.RoutedEventArgs)
            'Dim collectionProperty As Microsoft.LightSwitch.Details.Client.IScreenCollectionProperty = Me.Details.Properties.PersonFlagMultis
            'Dim intPageSize = collectionProperty.PageSize
            'collectionProperty.PageSize = 0

            'Dim collectionProperty2 As Microsoft.LightSwitch.Details.Client.IScreenCollectionProperty = Me.Details.Properties.Persons
            'Dim intPageSize2 = collectionProperty2.PageSize
            'collectionProperty2.PageSize = 0
            Dim dlg As New SaveFileDialog()

            dlg.Filter = "Excel files (.xlsx;.xls)|*.xlsx;*.xls|All Files (*.*)|*.*"
            dlg.FilterIndex = 1

            If dlg.ShowDialog() = True Then
                Dim message As String = Nothing

                Try
                    ' Create a new instance of the WorkbookManager class.
                    Dim manager As New WorkbookManager()

                    ' Add a workbook.
                    Dim workbook As Workbook = manager.Workbooks.Add()

                    If System.IO.Path.GetExtension(dlg.SafeFileName) = ".xls" Then
                        workbook.Version = ComponentPro.Excel.ExcelVersion.Excel97to2003
                    Else
                        workbook.Version = ComponentPro.Excel.ExcelVersion.Excel2007
                    End If
                    Dim sheet As Worksheet = workbook.Worksheets(0)

                    CreateChartData(sheet)

                    ' Hide gridlines.
                    'sheet.IsGridLinesVisible = False

                    Using fileStream As Stream = dlg.OpenFile()
                        workbook.SaveAs(fileStream)
                        ' Close the document.
                        workbook.Close()
                    End Using

                    message = "File saved successfully."

                Catch ex As Exception
                    message = "Error saving Excel file. Error: " & ex.Message
                End Try
                Me.ShowMessageBox(message, "Error Message", MessageBoxOption.Ok)
                'lblMessage.Content = message
            End If
            'collectionProperty.PageSize = intPageSize
            'collectionProperty2.PageSize = intPageSize2
        End Sub

        Private Function GetCSV() As String
            Dim csv As New StringBuilder()

            Dim i As Integer = 0

            'Dim xflag = From f In DataWorkspace.ApplicationData.PersonFlagMultis
            '                Order By f.Person
            Dim xflag As EntityCollection(Of PersonFlagMulti)
            'Dim xflagquery As IDataServiceQueryable(Of PersonFlagMulti)

            For Each person In Persons
                '//HEADER
                If i = 0 Then
                    Dim c As Integer = 0
                    For Each prop In person.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()
                        If prop.DisplayName <> "GPSCoordinates" Then
                            If c > 0 Then
                                'csv.Append(vbTab)
                                csv.Append(",")
                            End If
                            c = c + 1
                            csv.Append(prop.DisplayName)
                        End If
                    Next
                End If
                csv.AppendLine("")

                '//DATA ROWS

                Dim c1 As Integer = 0
                For Each prop In person.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()
                    If prop.DisplayName <> "GPSCoordinates" Then
                        If c1 > 0 Then
                            'csv.Append(vbTab)
                            csv.Append(",")
                        End If
                        c1 = c1 + 1
                        csv.Append(prop.Value)
                    End If
                Next
                i = i + 1
            Next

            If csv.Length > 0 Then
                Return csv.ToString(0, csv.Length - 1)
            Else
                Return ""
            End If
        End Function

        '//Selected columns
        Private Function GetCSV_old() As String
            Dim csv As New StringBuilder()
            Dim i As Integer = 0

            For Each a In Persons
                For Each xflag In a.FlagQuery
                    Me.ShowMessageBox(xflag.ToString(), "CSV Export", MessageBoxOption.Ok)
                Next
                If i = 0 Then
                    csv.AppendFormat("Last Name" & "," & "First Name" & System.Environment.NewLine, a)
                End If
                csv.AppendFormat(a.LastName & "," & a.FirstName & System.Environment.NewLine, a)
                i = i + 1
            Next

            If csv.Length > 0 Then
                Return csv.ToString(0, csv.Length - 1)
            Else
                Return ""
            End If
        End Function

        Private Sub CreateChartData(ByVal sheet As Worksheet)
            Dim message As String = Nothing
            Dim flagname As String = Nothing
            Dim flagnum As Integer = 0
            Dim person As Person = Nothing
            Dim xflag As PersonFlagMulti
            Dim flagsloc As String = Nothing
            Try
                Dim i As Integer = 0
                Dim r2 As Integer = 2
                For Each person In Persons
                    '//HEADER
                    If i = 0 Then
                        Dim c As Integer = 0
                        For Each prop In person.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()
                            If prop.DisplayName <> "Row Version" Then
                                sheet.Range(ChrW(AscW("A") + c) & "1").Text = prop.DisplayName
                                c = c + 1
                            End If
                        Next
                        sheet.Range(ChrW(AscW("A") + c) & "1").Text = "Flags"
                        flagsloc = c
                    End If
                    '//DATA ROWS
                    Dim c1 As Integer = 0
                    For Each prop2 In person.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()
                        If prop2.DisplayName <> "Row Version" Then
                            If Not IsNothing(prop2.Value) Then
                                sheet.Range(ChrW(AscW("A") + c1) & r2).Text = prop2.Value.ToString()
                            End If
                            c1 = c1 + 1
                        End If
                    Next
                    'flagname = ""
                    'flagnum = 0
                    'For Each xflag In person.Flag
                    'If flagnum = 0 Then
                    'flagname = xxflag.PersonFlag.Flag
                    'Else
                    'If xflag.PersonFlag.Flag Then
                    'flagname = flagname & "," & xflag.PersonFlag.Flag
                    'End If

                    'End If
                    'If Not IsNothing(flagname) Then
                    'sheet.Range(ChrW(AscW("A") + c1) & r2).Text = flagname
                    'End If
                    'flagnum = flagnum + 1

                    'Next
                    'Dim query As IDataServiceQueryable(Of PersonFlagMulti)
                    'Dim query = From x In person.Flag
                    'Where.Id = person.Id
                    '        Select x
                    'For Each xflag In query
                    'If xflag.Person.Id = person.Id Then
                    'If flagnum = 0 Then
                    'flagname = xflag.PersonFlag.Flag
                    'Else
                    'flagname = flagname & "," & xflag.PersonFlag.Flag
                    'End If
                    'If Not IsNothing(flagname) Then
                    'sheet.Range(ChrW(AscW("A") + c1) & r2).Text = flagname
                    'End If
                    'flagnum = flagnum + 1
                    'End If
                    'Next
                    r2 = r2 + 1
                    i = i + 1
                Next

                For Each xflag In PersonFlagMultis
                    For xcell = 2 To Persons.Count + 1
                        If xflag.Person.Id = sheet.Range(xcell, 1).Value Then
                            If sheet.Range(ChrW(AscW("A") + flagsloc) & xcell).Text = Nothing Then
                                sheet.Range(ChrW(AscW("A") + flagsloc) & xcell).Text = xflag.PersonFlag.Flag
                            Else
                                sheet.Range(ChrW(AscW("A") + flagsloc) & xcell).Text = sheet.Range(ChrW(AscW("A") + flagsloc) & xcell).Text & "," & xflag.PersonFlag.Flag
                            End If
                        End If
                    Next
                Next

                sheet.UsedRange.AutoFitColumns()
            Catch ex As Exception
                message = "Error building Excel file. Error: " & ex.Message & " - " & ex.Data.ToString
                'Me.ShowMessageBox(person.FullName & " " & xflag.Id & " " & xflag.PersonFlag.Flag, "flag", MessageBoxOption.Ok)
                Me.ShowMessageBox(message, "Error Message", MessageBoxOption.Ok)

                'Dim st As New StackTrace(ex)
                'st = New StackTrace(ex, True)
                'Me.ShowMessageBox("Line: " & st.GetFrame(0).GetFileLineNumber().ToString, "Error", MessageBoxOption.Ok)
            End Try
        End Sub

    End Class

End Namespace
